package com.capgemini.orf.importing;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class AssignmentImporter {
	private final static String FILE_TO_IMPORT = "Assignment list Belgium 06-10-2011.xls";
	private final static int ROW_START_INDEX = 1;
	private final static int COLUMN_START_INDEX = 1;
	private final static int COLUMN_END_INDEX = 22;

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		AssignmentImporter importer = new AssignmentImporter();
		try {
			List<Assignment> assignments = importer.importFrom(FILE_TO_IMPORT);
			System.out.println("DONE...");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public List<Assignment> importFrom(String fileName) throws IOException {
		InputStream myxls = new FileInputStream(fileName);
		HSSFWorkbook wb = new HSSFWorkbook(myxls);
		HSSFSheet sheet = wb.getSheet("Assignment and Valorization lis");

		List<Assignment> assignments = new ArrayList<Assignment>();

		// for each row in sheet, create new object
		for (int rowIndex = ROW_START_INDEX;; rowIndex++) {
			HSSFRow row = sheet.getRow(rowIndex);

			if (row != null) {
				Assignment assignment = readAssignmentRow(row);

				if (assignment != null) {
					assignments.add(assignment);
				} else {
					break;
				}
			} else {
				break;
			}
		}

		return assignments;
	}

	private Assignment readAssignmentRow(HSSFRow row) {
		Assignment assignment = new Assignment();
		int columnIndex = COLUMN_START_INDEX;
		assignment.setEmployeeNumber(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		String name = readValueAt(row.getCell(columnIndex));
		if (name != null) {
			String[] temp = name.split(",");
			if (temp.length > 0 && temp[0] != null)
				assignment.setSurname(temp[0].trim());
			if (temp.length > 1 && temp[1] != null)
				assignment.setFirstName(temp[1].trim());
		}
		columnIndex++;
		assignment.setUnit(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		String customerNumber = readValueAt(row.getCell(columnIndex));
		if (customerNumber != null)
			assignment.setCustomerNumber(Integer.parseInt(customerNumber));
		columnIndex++;
		assignment.setFunction(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setQualification(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setCustomerName(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setProjectUnit(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setProjectCode(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setProjectName(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setTaskNumber(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setTaskName(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		assignment.setAssignmentStartingDate(readValueAt(row
				.getCell(columnIndex)));
		columnIndex++;
		assignment
				.setAssignmentEndingDate(readValueAt(row.getCell(columnIndex)));
		columnIndex++;
		String value = readValueAt(row.getCell(columnIndex));
		if (value != null)
			assignment.setPercentageOfAssignment(Float.parseFloat(value));
		columnIndex++;
		value = readValueAt(row.getCell(columnIndex));
		if (value != null)
			assignment.setValorizationTariff(new BigDecimal(value));
		columnIndex++;
		value = readValueAt(row.getCell(columnIndex));
		if (value != null)
			assignment.setCor(new BigDecimal(value));
		columnIndex++;
		value = readValueAt(row.getCell(columnIndex));
		if (value != null)
			assignment.setBenefit(new BigDecimal(value));
		columnIndex++;
		value = readValueAt(row.getCell(columnIndex));
		if (value != null)
			assignment.setNumberOfWeekOnProject((int) Math.round(Double
					.parseDouble(value)));
		columnIndex++;
		value = readValueAt(row.getCell(columnIndex));
		if (value != null)
			assignment
					.setPercentageToExtendTheAssignment(new BigDecimal(value));
		columnIndex++;
		assignment.setComments(readValueAt(row.getCell(columnIndex)));

		return assignment;
	}

	/**
	 * @param cell
	 * @return a String representing the value of the provided cell.
	 */
	private String getString(final HSSFCell cell) {
		HSSFRichTextString richString = cell.getRichStringCellValue();
		String text = richString != null ? richString.getString() : null;

		if (text == null || "".equals(text.trim()))
			return null;

		return text;
	}

	/**
	 * @return a string representation of the cell.
	 * 
	 *         This method looks at the type of the cell and returns a simple
	 *         representation for this type.
	 * 
	 *         Dates are displayed as dd-MMMM-yyyy. Formula's are returned as
	 *         formula, not as the result of the formula. Blank cells return an
	 *         empty string. Boolean cells return true or false. Errors or other
	 *         cell types return null.
	 */
	private String readValueAt(final HSSFCell cell) {
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
					return sdf.format(cell.getDateCellValue());
				} else {
					return cell.getNumericCellValue() + "";
				}
			case HSSFCell.CELL_TYPE_STRING:
				return getString(cell);

			case HSSFCell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();

			case HSSFCell.CELL_TYPE_BLANK:
				return null;

			case HSSFCell.CELL_TYPE_BOOLEAN:
				return Boolean.toString(cell.getBooleanCellValue());

			case HSSFCell.CELL_TYPE_ERROR:
				return null;

			default:
				return null;
			}
		} else {
			return null;
		}
	}
}
